/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ssi.mrms.dao.Impl;

import com.ssi.mrms.dao.RoomProfileDao;
import com.ssi.mrms.dao.mapping.RoomProfileMapping;
import com.ssi.mrms.model.DataTableModel;
import com.ssi.mrms.model.RMrmlstModel;
import com.ssi.mrms.model.RMrmownModel;
import com.ssi.mrms.model.RMrmtntModel;
import com.ssi.mrms.utils.CommonUtils;
import com.ssi.mrms.utils.SecurityUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import static java.text.MessageFormat.format;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

/**
 *
 * @author jirapatjantsakool
 */
@Repository
public class RoomProfileDaoImpl implements RoomProfileDao {

    private static final Logger logger = Logger.getLogger(RoomProfileDaoImpl.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public RMrmlstModel findRoomProfileByAddress(RMrmlstModel roomProfile) {

        String sql = "SELECT * FROM rmrmlst WHERE rlstaddr=?";
        try {
            roomProfile = jdbcTemplate.queryForObject(sql, new Object[]{roomProfile.getRlstaddr()}, new RoomProfileMapping());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return roomProfile == null ? new RMrmlstModel() : roomProfile;
    }

    @Override
    public List<RMrmlstModel> findRoomProfileByCriteria(RMrmlstModel roomProfile) {

        String sql = "SELECT * FROM rmrmlst WHERE 1=1 ";

        if (null != roomProfile) {
            if (StringUtils.isNotBlank(roomProfile.getRlsttower())) {
                sql = sql.concat(" and RLSTTOWER  = '" + roomProfile.getRlsttower() + "' ");
            }
            if (StringUtils.isNotBlank(roomProfile.getRlstfloor())) {
                sql = sql.concat(" and RLSTFLOOR  = '" + roomProfile.getRlstfloor() + "' ");
            }
            if (StringUtils.isNotBlank(roomProfile.getRlstunitno())) {
                sql = sql.concat(" and RLSTUNITNO  = '" + roomProfile.getRlstunitno() + "' ");
            }
            if (StringUtils.isNotBlank(roomProfile.getRlstaddr())) {
                sql = sql.concat(" and RLSTADDR  = '" + roomProfile.getRlstaddr() + "' ");
            }
        }

        logger.debug(sql);
        List<RMrmlstModel> list = jdbcTemplate.query(sql, new Object[]{}, new RoomProfileMapping());

        return CommonUtils.isNullOrEmtry(list) ? new ArrayList<RMrmlstModel>() : list;
    }

    @Override
    public void create(final RMrmlstModel model) throws Exception {

        final String sql = "INSERT INTO rmrmlst(rlrmid,rlsttower,rlstfloor,rlstunitno,rlstaddr) VALUES(?,?,?,?,?)";

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(format(sql), Statement.RETURN_GENERATED_KEYS);
                int i = 0;
                ps.setInt(++i, model.getRlrmid());
                ps.setString(++i, model.getRlsttower());
                ps.setString(++i, model.getRlstfloor());
                ps.setString(++i, model.getRlstunitno());
                ps.setString(++i, model.getRlstaddr());
                return ps;
            }

        }, keyHolder);
    }

    @Override
    public void create(final RMrmownModel model) throws Exception {
        
        final String sql = "INSERT INTO rmrmown(rownprenm,"
                + "rownprlnm,rownfsenm,rownfslnm,rownmdenm,rownmdlnm,"
                + "rownlsenm,rownlslnm,rownnation,rownagt,rowntfsdat,"
                + "rownpassno,rowntel,rowntel2,rownaddr,rownemail,rownstat,"
                + "rowncreby,rowncredat,rownupdby,rownupddat) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
        
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(format(sql), Statement.RETURN_GENERATED_KEYS);
                int i = 0;
                ps.setString(++i, model.getRownprenm());
                ps.setString(++i, model.getRownprlnm());
                ps.setString(++i, model.getRownfsenm());
                ps.setString(++i, model.getRownfslnm());
                ps.setString(++i, model.getRownmdenm());
                ps.setString(++i, model.getRownmdlnm());
                ps.setString(++i, model.getRownlsenm());
                ps.setString(++i, model.getRownlslnm());
                ps.setString(++i, model.getRownnation());
                ps.setString(++i, model.getRownagt());
                ps.setDate(++i, new java.sql.Date(model.getRowntfsdat().getTime()));
                ps.setString(++i, model.getRownpassno());
                ps.setString(++i, model.getRowntel());
                ps.setString(++i, model.getRowntel2());
                ps.setString(++i, model.getRownaddr());
                ps.setString(++i, model.getRownemail());
                ps.setString(++i, model.getRownstat());
                ps.setString(++i, model.getRowncreby());
                ps.setDate(++i, new java.sql.Date(model.getRowncredat().getTime()));
                ps.setString(++i, model.getRownupdby());
                ps.setDate(++i, new java.sql.Date(model.getRownupddat().getTime()));
                return ps;
            }

        }, keyHolder);
    }

    @Override
    public void create(final RMrmtntModel model) throws Exception {
       
        final String sql = "INSERT INTO rmrmtnt(rtntprenm,rtntprlnm,rtntfsenm,rtntfslnm,"
                + "rtntmdenm,rtntmdlnm,rtntlsenm,rtntlslnm,rtntnation,"
                + "rtntpassno,rtnttfsdat,rtnttel,rtntemail,rtntremrk,"
                + "rtntstat,rtntcreby,rtntcredat,rtntupdby,rtntupddat) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(format(sql), Statement.RETURN_GENERATED_KEYS);
                int i = 0;
                ps.setString(++i, model.getRtntprenm());
                ps.setString(++i, model.getRtntprlnm());
                ps.setString(++i, model.getRtntfsenm());
                ps.setString(++i, model.getRtntfslnm());
                ps.setString(++i, model.getRtntmdenm());
                ps.setString(++i, model.getRtntmdlnm());
                ps.setString(++i, model.getRtntlsenm());
                ps.setString(++i, model.getRtntlslnm());
                ps.setString(++i, model.getRtntnation());
                ps.setString(++i, model.getRtntpassno());
                ps.setDate(++i, new java.sql.Date(model.getRtnttfsdat().getTime()));
                ps.setString(++i, model.getRtnttel());
                ps.setString(++i, model.getRtntemail());
                ps.setString(++i, model.getRtntemail());
                ps.setString(++i, model.getRtntremrk());
                ps.setString(++i, model.getRtntstat());
                ps.setString(++i, model.getRtntcreby());
                ps.setDate(++i, new java.sql.Date(model.getRtntcredat().getTime()));
                ps.setString(++i, model.getRtntupdby());
                ps.setDate(++i, new java.sql.Date(model.getRtntupddat().getTime()));
                return ps;
            }

        }, keyHolder);
        
    }

    @Override
    public boolean update(RMrmlstModel model) {

        final String SQL_UPDATE_RMRMLST = "UPDATE rmrmlst SET rlsttower=?,rlstfloor=?,rlstunitno=?,rlstaddr=?,rlsttype=? WHERE rlrmid=?";

        int row = jdbcTemplate.update(format(SQL_UPDATE_RMRMLST),
                new Object[]{
                    model.getRlsttower(),
                    model.getRlstfloor(),
                    model.getRlstunitno(),
                    model.getRlstaddr(),
                    model.getRlrmid()
                });
        return row > 0;
    }

    @Override
    public boolean update(RMrmownModel model) {
    	

        final String SQL_INSERT_RMRMOWN = "INSERT INTO rmrmown(rownprenm,"
                + "rownprlnm,rownfsenm,rownfslnm,rownmdenm,rownmdlnm,"
                + "rownlsenm,rownlslnm,rownnation,rownagt,rowntfsdat,"
                + "rownpassno,rowntel,rowntel2,rownaddr,rownemail,rownstat,"
                + "rowncreby,rowncredat,rownupdby,rownupddat, rlrmid) VALUES(?,?,?,?,?  " +
                " ,?,?,?,?,?,  ?,?,?,?,?,  ?,?,?,CURDATE(), ? ,CURDATE(),?) ";
        
        final String SQL_UPDATE_RMRMOWN = " UPDATE rmrmown SET rownprenm=?,rownprlnm=?,rownfsenm=?,rownfslnm=?," +
        		" rownmdenm=?,rownmdlnm=?,rownlsenm=?,rownlslnm=?,rownnation=?,rownagt=?,rowntfsdat=?," +
        		" rownpassno=?,rowntel=?,rowntel2=?,rownaddr=?,rownemail=?,rownstat=? " +
        		" ,rownupdby=?,rownupddat=CURDATE() WHERE rlrmid=?";
        
        
        int count = jdbcTemplate.queryForInt(" SELECT COUNT(1) FROM rmrmown WHERE RLRMID = '"+model.getRlrmid()+"' ");
        if(count > 0){
        	jdbcTemplate.update(SQL_UPDATE_RMRMOWN ,
                    new Object[]{
                        model.getRownprenm(),
                        model.getRownprlnm(),
                        model.getRownfsenm(),
                        model.getRownfslnm(),
                        model.getRownmdenm(),
                        model.getRownmdlnm(),
                        model.getRownlsenm(),
                        model.getRownlslnm(),
                        model.getRownnation(),
                        model.getRownagt(),
                        null != model.getRowntfsdat()? new java.sql.Date(model.getRowntfsdat().getTime()): null,
                        model.getRownpassno(),
                        model.getRowntel(),
                        model.getRowntel2(),
                        model.getRownaddr(),
                        model.getRownemail(),
                        model.getRownstat(),
                        SecurityUtils.getUsername(),
                        model.getRlrmid()
                    });
            
        }else{
        	jdbcTemplate.update(SQL_INSERT_RMRMOWN ,
                    new Object[]{
            		  model.getRownprenm(),
                      model.getRownprlnm(),
                      model.getRownfsenm(),
                      model.getRownfslnm(),
                      model.getRownmdenm(),
                      model.getRownmdlnm(),
                      model.getRownlsenm(),
                      model.getRownlslnm(),
                      model.getRownnation(),
                      model.getRownagt(),
                      model.getRowntfsdat() != null? new java.sql.Date(model.getRowntfsdat().getTime()) : null,
                      model.getRownpassno(),
                      model.getRowntel(),
                      model.getRowntel2(),
                      model.getRownaddr(),
                      model.getRownemail(),
                      model.getRownstat(),
                      SecurityUtils.getUsername(),
                      SecurityUtils.getUsername(),
        				model.getRlrmid()
                    });
        }
        
        return true;
        
        
      
    }

    @Override
    public boolean update(RMrmtntModel model) {
    	
        final String SQL_INSERT_RMRMTNT = "INSERT INTO rmrmtnt(rtntprenm,rtntprlnm,rtntfsenm,rtntfslnm,"
                + "rtntmdenm,rtntmdlnm,rtntlsenm,rtntlslnm,rtntnation,"
                + "rtntpassno,rtnttfsdat,rtnttel,rtntemail,rtntremrk,"
                + "rtntstat,rtntcreby,rtntcredat,rtntupdby,rtntupddat,rlrmid) " +
                "VALUES(?,?,?,?,?,  ?,?,?,?,?, ?,?,?,?,?, ?,CURDATE(),?,CURDATE(),?) ";

        final String SQL_UPDATE_RMRMTNT = " UPDATE rmrmtnt SET " +
        		" rtntprenm=? , rtntprlnm=? , rtntfsenm=? , rtntfslnm=? , "
                        + "rtntmdenm=? , rtntmdlnm=? , rtntlsenm=? , rtntlslnm=? , rtntnation=? , "
                        + "rtntpassno=? , rtnttfsdat=? , rtnttel=? , rtntemail=? , rtntremrk=? , "
                        + "rtntstat=?, rtntupdby=? , rtntupddat=CURDATE() WHERE rlrmid=?";
        
        int count = jdbcTemplate.queryForInt(" SELECT COUNT(1) FROM rmrmtnt WHERE RLRMID = '"+model.getRlrmid()+"' ");
        if(count > 0){
        	jdbcTemplate.update(SQL_UPDATE_RMRMTNT,
                new Object[]{
                    model.getRtntprenm(),
                    model.getRtntprlnm(),
                    model.getRtntfsenm(),
                    model.getRtntfslnm(),
                    model.getRtntmdenm(),
                    model.getRtntmdlnm(),
                    model.getRtntlsenm(),
                    model.getRtntlslnm(),
                    model.getRtntnation(),
                    model.getRtntpassno(),
                    null!=model.getRtnttfsdat()? new java.sql.Date(model.getRtnttfsdat().getTime()):null,
                    model.getRtnttel(),
                    model.getRtntemail(),
                    model.getRtntremrk(),
                    model.getRtntstat(),
                    SecurityUtils.getUsername(),
                    model.getRlrmid()
                });
        }else{
        	jdbcTemplate.update(SQL_INSERT_RMRMTNT,
                    new Object[]{
            		 model.getRtntprenm(),
                     model.getRtntprlnm(),
                     model.getRtntfsenm(),
                     model.getRtntfslnm(),
                     model.getRtntmdenm(),
                     model.getRtntmdlnm(),
                     model.getRtntlsenm(),
                     model.getRtntlslnm(),
                     model.getRtntnation(),
                     model.getRtntpassno(),
                     null!=model.getRtnttfsdat()? new java.sql.Date(model.getRtnttfsdat().getTime()):null,
                     model.getRtnttel(),
                     model.getRtntemail(),
                     model.getRtntremrk(),
                     model.getRtntstat(),
                     SecurityUtils.getUsername(),
                     SecurityUtils.getUsername(),
                     model.getRlrmid()     		
                    });
        }
        return true;
    }

    @Override
    public boolean delete(RMrmlstModel model) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public DataTableModel<RMrmlstModel> findDataTable(RMrmlstModel model, int iDisplayStart, int iDisplayLength) {

        DataTableModel<RMrmlstModel> dataTableModel = new DataTableModel<RMrmlstModel>();
        String sqlLimit = MessageFormat.format(" limit {0},{1} ", new Object[]{iDisplayStart, iDisplayLength});

        /* --- Where Condition  ---*/
        String sql_where = "";
        /* --- Room Condition  ----*/
        if (StringUtils.isNotBlank(model.getRlsttower())) {
            sql_where = sql_where.concat(" AND  a.RLSTTOWER = '" + model.getRlsttower() + "' ");
        }
        if (StringUtils.isNotBlank(model.getRlstfloor())) {
            sql_where = sql_where.concat(" AND  a.RLSTFLOOR = '" + model.getRlstfloor() + "' ");
        }
        if (StringUtils.isNotBlank(model.getRlstunitno())) {
            sql_where = sql_where.concat(" AND  a.RLSTUNITNO = '" + model.getRlstunitno() + "' ");
        }
        if (StringUtils.isNotBlank(model.getRlstaddr())) {
            sql_where = sql_where.concat(" AND  a.RLSTADDR = '" + model.getRlstaddr() + "' ");
        }

        /*--- Other Condition ----*/
//        if (StringUtils.isNotBlank(model.getOwnModel()!=null? model.getOwnModel().getRownfsenm() : null)) {
//        	sql_where = sql_where.concat(" AND A.ACCRIDUSRNO LIKE '%" + model.getOwnModel().getRownfsenm() + "%' ");
//        }
//        if (StringUtils.isNotBlank(model.getOwnModel()!=null? model.getOwnModel().getRownfsenm() : null)) {
//        	sql_where = sql_where.concat(" AND A.ACCRCRDID LIKE '%" + model.getAccrcrdid() + "%' ");
//        }
//        if (StringUtils.isNotBlank(model.getAccrlrno())) {
//        	sql_where = sql_where.concat(" AND A.ACCRLRNO LIKE '%" + model.getAccrlrno() + "%' ");
//        }
//        if (StringUtils.isNotBlank(model.getAccrstat())) {
//            sql_where = sql_where.concat(" AND A.ACCRSTAT = '" + model.getAccrstat() + "' ");
//        }

        /*--- Initial Query ---*/
        String sqlQuery = " SELECT * FROM rmrmlst a LEFT JOIN rmrmown b ON a.RLRMID= b.RLRMID LEFT JOIN rmrmtnt c ON a.RLRMID= c.RLRMID "
                + " WHERE 1=1 " + sql_where + sqlLimit;
        String sqlCount = " SELECT count(1) FROM rmrmlst a LEFT JOIN rmrmown b ON a.RLRMID= b.RLRMID LEFT JOIN rmrmtnt c ON a.RLRMID= c.RLRMID "
                + " WHERE 1=1 " + sql_where;

        // --- #1 Get data for datatable by limit row
        List<RMrmlstModel> list = jdbcTemplate.query(sqlQuery, new RoomProfileMapping());

        // --- #2 Get row number
        int count = jdbcTemplate.queryForInt(sqlCount);

        dataTableModel.setItems(list);
        dataTableModel.setTotal(count);
        dataTableModel.setIndex(iDisplayStart);
        dataTableModel.setLength(iDisplayLength);

        System.out.println("SQL : " + sqlQuery);
        return dataTableModel;
    }

    @Override
    public RMrmlstModel findRoomProfileById(Integer id) {
        final String SQL_QUERY_RMRMLST_BY_ID = " SELECT * FROM rmrmlst a LEFT JOIN rmrmown b ON a.RLRMID= b.RLRMID LEFT JOIN rmrmtnt c ON a.RLRMID= c.RLRMID where a.rlrmid = ?";

        List<RMrmlstModel> model = jdbcTemplate.query(SQL_QUERY_RMRMLST_BY_ID, new Object[]{id}, new RoomProfileMapping());

        return model.size() <= 0 ? new RMrmlstModel() : model.get(0);
    }

}
